package com.example.excel;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.write.handler.CellWriteHandler;
import com.alibaba.excel.write.handler.SheetWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;

import java.io.FileOutputStream;
import java.util.ArrayList;
import java.util.List;

public class EasyExcelMergeExample {
    public static void main(String[] args) throws Exception {
        // 创建工作簿
        SXSSFWorkbook workbook = new SXSSFWorkbook();

        // 创建一些数据
        List<List<String>> data = new ArrayList<>();
        for (int i = 0; i < 10; i++) {
            List<String> row = new ArrayList<>();
            for (int j = 0; j < 5; j++) {
                row.add("Data " + i + "," + j);
            }
            data.add(row);
        }

        // 定义需要合并的区域
        List<MergeRegion> mergeRegions = new ArrayList<>();
        mergeRegions.add(new MergeRegion(1, 7, 0, 0));
        mergeRegions.add(new MergeRegion(1, 7, 1, 1));

        mergeRegions.add(new MergeRegion(1, 4, 2, 2));
        mergeRegions.add(new MergeRegion(5, 7, 2, 2));
        mergeRegions.add(new MergeRegion(1, 2, 3, 3));
        mergeRegions.add(new MergeRegion(3, 4, 3, 3));

//        mergeRegions.add(new MergeRegion(0, 2, 0, 0)); // 合并第0列的第0到第2行


        // 创建一个自定义写入处理器来应用合并区域
        SheetWriteHandler mergeHandler = new SheetWriteHandler() {
            @Override
            public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
                applyMergeRegions(writeWorkbookHolder, mergeRegions);
            }
        };

        CellWriteHandler cellWriteHandler = new CellWriteHandler() {
            @Override
            public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
                Sheet sheet = writeSheetHolder.getSheet();
                Workbook workbook = sheet.getWorkbook();
                if (!isHead) { // 非表头，设置单元格内容自动换行

                    CellStyle cellStyle = workbook.createCellStyle();
                    cellStyle.setWrapText(true); // 设置单元格自动换行

                    // 水平和垂直居中
//            cellStyle.setAlignment(HorizontalAlignment.CENTER);
                    cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);

                    cell.setCellStyle(cellStyle);
                }
                sheet.setColumnWidth(0, 30 * 256);
                sheet.setColumnWidth(1, 60 * 256);
                for (int i = 2; i < 5; i++) {
                    sheet.setColumnWidth(i, 40 * 256);
                }

            }
        };
        List<List<String>> header = new ArrayList<>();
        List<String> Header1 = new ArrayList<>();
        Header1.add("Header1");

        List<String> Header2 = new ArrayList<>();
        Header2.add("Header2");
        List<String> Header3 = new ArrayList<>();
        Header3.add("Header3");
        List<String> Header4 = new ArrayList<>();
        Header4.add("Header4");
        List<String> Header5 = new ArrayList<>();
        Header5.add("Header5");
        header.add(Header1);
        header.add(Header2);
        header.add(Header3);
        header.add(Header4);
        header.add(Header5);


        // 写入数据到Excel文件
        EasyExcel.write(new FileOutputStream("workbook.xlsx"), data.getClass())
            .registerWriteHandler(mergeHandler)
            .registerWriteHandler(cellWriteHandler)
            .sheet("Sheet1")
            .head(header)
            .doWrite(data);

        // 关闭工作簿
        workbook.close();
    }

    private static void applyMergeRegions(WriteWorkbookHolder writeWorkbookHolder, List<MergeRegion> mergeRegions) {
        Sheet sheet = writeWorkbookHolder.getWorkbook().getSheetAt(0);
        for (MergeRegion mergeRegion : mergeRegions) {
            sheet.addMergedRegion(mergeRegion.toCellRangeAddress());
        }
    }
}